#!\usr\bin\perl
#database_demo.pl

use warnings;
use strict;
use DBI;


#check the connection to the database
my ($dbh, $sth_create, $sth_query, $sth_insert, $sth_delete, $id, $name, $email);
$dbh=DBI->connect('dbi:mysql:test','root','honghoang1') || 
   die "Error opening database: $DBI::errstr\n";
print "Hello\n";


#in the test database, create table persons
$sth_create=$dbh->prepare("CREATE TABLE person (
         id        INTEGER AUTO_INCREMENT PRIMARY KEY,
         name      VARCHAR(32) NOT NULL,
         email     VARCHAR(32) NOT NULL)");
$sth_create->execute();           # execute the statement
$sth_create->finish();            # finish the execution
print "All done\n";


#insert new records into the person table
$sth_insert=$dbh->prepare ("INSERT INTO person (name, email) VALUES ('Doan Hong Quan' , 'hongquan2512\@gmail\.com');");
$sth_insert->execute();
$sth_insert=$dbh->prepare ("INSERT INTO person (name, email) VALUES ('Akihito' , akihito\@kayac\.com');");
$sth_insert->execute();
$sth_insert->finish();


#query, and returns results
$sth_query=$dbh->prepare("SELECT * from person;") ||
   die "Prepare failed: $DBI::errstr\n";
$sth_query->execute() ||
   die "Couldn't execute query: $DBI::errstr\n";
while (( $id, $name, $email) = $sth_query ->fetchrow_array) {
   print "$name has email $email\n";
}
$sth_query->finish();


#delete first line in the table
$sth_delete=$dbh->prepare("DELETE FROM person WHERE id='1';") ||
   die "Prepare failed: $DBI::errstr\n";
$sth_delete->execute() ||
   die "Couldn't execute query: $DBI::errstr\n";
$sth_delete->finish();


#disconnect with the database
$dbh->disconnect || die "Failed to disconnect\n";